package com.slipper.groovy.util;

import com.slipper.PaasContext;
import com.slipper.constants.Constants;
import com.slipper.exception.CommonException;
import com.slipper.util.DatabaseUtil;
import com.slipper.util.MD5;
import com.slipper.util.Tool;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class JDBCUtil implements AutoCloseable {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    private final List<Connection> connList = new ArrayList<>();
    private final Map<String, Connection> connMap = new HashMap<>();

    public synchronized Connection getConnection(String driver, String url, String username, String password) throws Exception {
        return getConnection(driver, url, username, password, false);
    }

    public synchronized Connection getConnection(String driver, String url, String username, String password, boolean requireNew) throws Exception {
        if (requireNew) {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);
            connList.add(conn);
            return conn;
        }

        String key = MD5.encode(driver + url + username + password);
        Connection conn = connMap.get(key);
        if (conn == null) {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);
            connMap.put(key, conn);
        }
        return conn;
    }

    public synchronized Connection getConnection() throws Exception {
        return getConnection(false);
    }

    public synchronized Connection getConnection(boolean requireNew) throws Exception {
        if (requireNew) {
            DataSource dataSource = (DataSource) PaasContext.getBean("dataSource");
            Connection conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            connList.add(conn);
            return conn;
        }

        String key = "local_connection";
        Connection conn = connMap.get(key);
        if (conn == null) {
            DataSource dataSource = (DataSource) PaasContext.getBean("dataSource");
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            connMap.put(key, conn);
        }
        return conn;
    }

    public int insert(String tableName, Map<String, Object> dataMap) throws Exception {
        return insert(getConnection(), tableName, dataMap);
    }

    public int insert(Connection conn, String tableName, Map<String, Object> dataMap) throws Exception {
        if (dataMap == null || dataMap.isEmpty()) {
            return 0;
        }

        String dbType = conn.getMetaData().getDatabaseProductName();
        List<String> fields = new ArrayList<>();
        List<Object> values = new ArrayList<>();
        for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
            fields.add(DatabaseUtil.wrapSymbol(dbType, entry.getKey()));
            values.add(entry.getValue());
        }
        String sql = "insert into " + DatabaseUtil.wrapSymbol(dbType, tableName)
                + "(" + String.join(",", fields) + ") values "
                + "(" + fields.stream().map(item -> "?").collect(Collectors.joining(", ")) + ")";
        return execute(conn, sql, values);
    }

    public int updateById(String tableName, Map<String, Object> dataMap) throws Exception {
        Map<String, Object> whereMap = new HashMap<>();
        whereMap.put("id", dataMap.remove("id"));
        return update(getConnection(), tableName, dataMap, whereMap);
    }

    public int update(String tableName, Map<String, Object> dataMap, String...props) throws Exception {
        return update(getConnection(), tableName, dataMap, props);
    }

    public int update(Connection conn, String tableName, Map<String, Object> dataMap, String...props) throws Exception {
        Map<String, Object> whereMap = new HashMap<>();
        for (String prop : props) {
            whereMap.put(prop, dataMap.remove(prop));
        }
        return update(conn, tableName, dataMap, whereMap);
    }

    public int update(String tableName, Map<String, Object> dataMap, Map<String, Object> whereMap) throws Exception {
        return update(getConnection(), tableName, dataMap, whereMap);
    }

    public int update(Connection conn, String tableName, Map<String, Object> dataMap, Map<String, Object> whereMap) throws Exception {
        if (dataMap == null || dataMap.isEmpty() || whereMap == null || whereMap.isEmpty()) {
            return 0;
        }

        String dbType = conn.getMetaData().getDatabaseProductName();
        List<String> fields = new ArrayList<>();
        List<Object> values = new ArrayList<>();
        for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
            fields.add(DatabaseUtil.wrapSymbol(dbType, entry.getKey()) + "=? ");
            values.add(entry.getValue());
        }

        List<String> whereFields = new ArrayList<>();
        for (Map.Entry<String, Object> entry : whereMap.entrySet()) {
            whereFields.add(DatabaseUtil.wrapSymbol(dbType, entry.getKey()) + "=? ");
            values.add(entry.getValue());
        }

        String sql = "update " + DatabaseUtil.wrapSymbol(dbType, tableName)
                + " set " + String.join(",", fields) + " "
                + " where " + String.join(" and ", whereFields) + " ";

        return execute(conn, sql, values);
    }

    public int execute(String sql) throws Exception {
        return execute(getConnection(), sql);
    }

    public int execute(String sql, List<Object> params) throws Exception {
        checkSql(sql);
        return executeSql(getConnection(), sql, params);
    }

    public int execute(Connection conn, String sql) throws Exception {
        checkSql(sql);
        return executeSql(conn, sql, null);
    }

    public int execute(Connection conn, String sql, List<Object> params) throws Exception {
        checkSql(sql);
        return executeSql(conn, sql, params);
    }

    private int executeSql(Connection conn, String sql, List<Object> params) throws Exception {
        int result;
        if (params != null && !params.isEmpty()) {
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                for (int i = 0; i < params.size(); i++) {
                    stmt.setObject(i + 1, params.get(i));
                }
                result = stmt.executeUpdate();
            }
        } else {
            try (Statement stmt = conn.createStatement()) {
                result = stmt.executeUpdate(sql);
            }
        }
        return result;
    }

    public int executeList(Connection conn, String sql, List<List<Object>> paramList) throws Exception {
        checkSql(sql);

        if (paramList == null || paramList.isEmpty()) {
            throw new CommonException("参数不能为空！");
        }

        return executeList(conn, sql, paramList, 3000);
    }

    public int executeList(Connection conn, String sql, List<List<Object>> paramList, int batchSize) throws Exception {
        checkLogin();
        checkSql(sql);

        if (batchSize <= 0 || batchSize > 5000) {
            throw new CommonException("批量事务提交记录数必须大于0且小于等于5000！");
        }
        if (paramList == null || paramList.isEmpty()) throw new CommonException("参数不能为空！");
        if (paramList.size() <= batchSize) {
            return batchExecute(conn, sql, paramList);
        } else {
            int result = 0;
            List<List<Object>> dataList = new ArrayList<>();
            for (List<Object> item : paramList) {
                dataList.add(item);
                if (dataList.size() >= batchSize) {
                    result += batchExecute(conn, sql, dataList);
                    dataList = new ArrayList<>();
                }
            }
            if (!dataList.isEmpty()) {
                result += batchExecute(conn, sql, dataList);
            }
            return result;
        }
    }

    private int batchExecute(Connection conn, String sql, List<List<Object>> paramList) throws Exception {
        int result = 0;
        try(PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (List<Object> params : paramList) {
                for (int i = 0; i < params.size(); i++) {
                    stmt.setObject(i + 1, params.get(i));
                }
                stmt.addBatch();
            }
            int[] resultList = stmt.executeBatch();
            for (int item : resultList) {
                result += item;
            }
        }
        return result;
    }

    public Map<String, Object> queryById(String tableName, Serializable id) throws Exception {
        return queryBy(tableName, "id", id);
    }

    public Map<String, Object> queryBy(String tableName, String idProp, Serializable id) throws Exception {
        return queryBy(getConnection(), tableName, new HashMap<>(){{
            put(idProp, id);
        }});
    }

    public Map<String, Object> queryBy(String tableName, Map<String, Object> paramMap) throws Exception {
        return queryBy(getConnection(), tableName, paramMap);
    }

    public Map<String, Object> queryBy(Connection conn, String tableName, Map<String, Object> paramMap) throws Exception {
        String dbType = conn.getMetaData().getDatabaseProductName();

        List<String> fields = new ArrayList<>();
        List<Object> values = new ArrayList<>();
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            fields.add(DatabaseUtil.wrapSymbol(dbType, entry.getKey()) + "=? ");
            values.add(entry.getValue());
        }

        String sql = "select * from " + DatabaseUtil.wrapSymbol(dbType, tableName)
                + " where " + String.join(" and ", fields) + " ";
        return queryOne(conn, sql, values);
    }

    public Map<String, Object> queryOne(String sql) throws Exception {
        return queryOne(sql, null);
    }

    public Map<String, Object> queryOne(String sql, List<Object> paramList) throws Exception {
        return queryOne(getConnection(), sql, paramList);
    }

    public Map<String, Object> queryOne(Connection conn, String sql, List<Object> paramList) throws Exception {
        List<Map<String, Object>> dataList = queryList(conn, sql, paramList);
        if (dataList == null || dataList.isEmpty()) {
            return null;
        } else {
            return dataList.getFirst();
        }
    }

    public List<Map<String, Object>> queryList(String sql) throws Exception {
        return queryListBySql(getConnection(), sql, null);
    }

    public List<Map<String, Object>> queryList(Connection conn, String sql) throws Exception {
        return queryListBySql(conn, sql, null);
    }

    public List<Map<String, Object>> queryList(String sql, List<Object> paramList) throws Exception {
        return queryList(getConnection(), sql, paramList);
    }

    public List<Map<String, Object>> queryList(Connection conn, String sql, List<Object> params) throws Exception {
        return queryListBySql(conn, sql, params);
    }

    private List<Map<String, Object>> queryListBySql(Connection conn, String sql, List<Object> params) throws Exception {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    stmt.setObject(i + 1, params.get(i));
                }
            }

            ResultSet resultSet = stmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while(resultSet.next()) {
                Map<String, Object> result = new HashMap<>();
                for (int i=1; i<=columnCount; i++) {
                    result.put(metaData.getColumnLabel(i).toLowerCase(), resultSet.getObject(i));
                }
                resultList.add(result);
            }

        }
        return resultList;
    }

    public long queryCount(String sql) throws Exception {
        return queryCount(sql, null);
    }

    public long queryCount(String sql, List<Object> params) throws Exception {
        List<Map<String, Object>> dataList = queryList(sql, params);
        if (dataList == null || dataList.isEmpty()) return 0;

        Map<String, Object> data = dataList.getFirst();
        if (data == null || data.isEmpty()) return 0;

        if (data.containsKey("cnt") && data.get("cnt") != null) {
            return Long.parseLong(data.get("cnt").toString());
        } else if (data.containsKey("CNT") && data.get("CNT") != null) {
            return Long.parseLong(data.get("CNT").toString());
        } else if (data.size() == 1) {
            return Long.parseLong(data.values().iterator().next().toString());
        }

        return 0;
    }

    public void runDDL(Connection conn, String ddl) throws Exception {
        checkSql(ddl);
        executeDDL(conn, ddl);
    }

    private void executeDDL(Connection conn, String ddl) throws Exception {
        checkLogin();
        Statement stmt = conn.createStatement();
        stmt.executeUpdate(ddl);
        stmt.close();
    }

    public void checkLogin() {
        Constants.getCurrentUser();
    }

    public void checkSql(String sql) {
        if (Tool.isBlank(sql)) throw new CommonException("sql不能为空！");
    }

    public void commit() throws Exception {
        for (Connection conn : connMap.values()) {
            conn.commit();
        }

        for (Connection conn : connList) {
            conn.commit();
        }
    }

    public void rollback() {
        for (Connection conn : connMap.values()) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                logger.error("事务回滚失败！", e);
            }
        }

        for (Connection conn : connList) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                logger.error("事务回滚失败！", e);
            }
        }
    }

    public void close() {
        for (Connection conn : connMap.values()) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                logger.error("连接关闭失败！", e);
            }
        }
        connMap.clear();

        for (Connection conn : connList) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                logger.error("连接关闭失败！", e);
            }
        }
        connList.clear();
    }
}
